admin / Strike

-- =====================================================================
-- StrikeXi v3 — Multi-Framework Cyber Maturity Assessment Platform
-- Relational schema: users, frameworks, taxonomy (objectives ->
-- principles), questions, assessments, answers, remediation mappings,
-- queued remediation actions, evidence/notes, score revisions, audit log.
--
-- v3 generalises the v2 (CAF-only) schema to support MULTIPLE maturity
-- frameworks (e.g. NCSC CAF, UK Cyber Security & Resilience Bill).
-- The taxonomy tables keep their `caf_*` names for continuity but are now
-- generic "framework level 1 (objective) -> level 2 (principle)" tables,
-- scoped by `framework_id`.
-- =====================================================================

CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- ---------- Frameworks (v3) ----------
-- A framework is a self-contained maturity model (its own objectives,
-- principles, questions and remediations). Adding a new framework is pure
-- data: insert a row here plus its taxonomy/question rows — no code change.
CREATE TABLE IF NOT EXISTS frameworks (
    id          TEXT PRIMARY KEY,            -- 'caf', 'csrb', ...
    name        TEXT NOT NULL,
    description TEXT,
    version     TEXT,
    sort_order  INT NOT NULL DEFAULT 0,
    is_active   BOOLEAN NOT NULL DEFAULT TRUE
);

-- ---------- Users & Authentication ----------
CREATE TABLE IF NOT EXISTS users (
    id                   UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    username             TEXT UNIQUE NOT NULL,
    password_hash        TEXT NOT NULL,
    full_name            TEXT,
    role                 TEXT NOT NULL DEFAULT 'assessor',  -- user | assessor | admin
    is_active            BOOLEAN NOT NULL DEFAULT TRUE,
    must_change_password BOOLEAN NOT NULL DEFAULT FALSE,
    mfa_secret           TEXT,                  -- base32 TOTP secret (null when no MFA)
    mfa_enabled          BOOLEAN NOT NULL DEFAULT FALSE,
    mfa_required         BOOLEAN NOT NULL DEFAULT FALSE,  -- admin-enforced MFA
    -- V2 self-service signup profile fields
    first_name           TEXT,
    surname              TEXT,
    company_name         TEXT,
    email                TEXT,
    contact_number       TEXT,
    -- How the account was created: 'self_service' (public signup) or 'admin'
    signup_origin        TEXT NOT NULL DEFAULT 'admin',
    -- True until the user passes their very first successful login (drives
    -- first-login MFA enforcement for self-service signups).
    first_login_pending  BOOLEAN NOT NULL DEFAULT FALSE,
    created_at           TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at           TIMESTAMPTZ NOT NULL DEFAULT now(),
    last_login           TIMESTAMPTZ
);

-- Idempotent migration for databases created before user-admin/MFA support.
ALTER TABLE users ADD COLUMN IF NOT EXISTS must_change_password BOOLEAN NOT NULL DEFAULT FALSE;
ALTER TABLE users ADD COLUMN IF NOT EXISTS mfa_secret   TEXT;
ALTER TABLE users ADD COLUMN IF NOT EXISTS mfa_enabled  BOOLEAN NOT NULL DEFAULT FALSE;
ALTER TABLE users ADD COLUMN IF NOT EXISTS mfa_required BOOLEAN NOT NULL DEFAULT FALSE;
ALTER TABLE users ADD COLUMN IF NOT EXISTS updated_at   TIMESTAMPTZ NOT NULL DEFAULT now();
ALTER TABLE users ADD COLUMN IF NOT EXISTS last_login   TIMESTAMPTZ;
-- V2 idempotent migrations
ALTER TABLE users ADD COLUMN IF NOT EXISTS first_name          TEXT;
ALTER TABLE users ADD COLUMN IF NOT EXISTS surname             TEXT;
ALTER TABLE users ADD COLUMN IF NOT EXISTS company_name        TEXT;
ALTER TABLE users ADD COLUMN IF NOT EXISTS email               TEXT;
ALTER TABLE users ADD COLUMN IF NOT EXISTS contact_number      TEXT;
ALTER TABLE users ADD COLUMN IF NOT EXISTS signup_origin       TEXT NOT NULL DEFAULT 'admin';
ALTER TABLE users ADD COLUMN IF NOT EXISTS first_login_pending BOOLEAN NOT NULL DEFAULT FALSE;

-- ---------- CAF Taxonomy: Objectives -> Principles ----------
-- Objectives: A (Managing security risk), B (Protecting against cyber attack),
--             C (Detecting cyber security events), D (Minimising impact)
CREATE TABLE IF NOT EXISTS caf_objectives (
    id           TEXT PRIMARY KEY,            -- 'A','B',... or 'CSRB-A' ...
    framework_id TEXT NOT NULL DEFAULT 'caf' REFERENCES frameworks(id),
    title        TEXT NOT NULL,
    description  TEXT,
    sort_order   INT NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS caf_principles (
    id           TEXT PRIMARY KEY,           -- 'A1','A2',... or 'CSRB-A1' ...
    framework_id TEXT NOT NULL DEFAULT 'caf' REFERENCES frameworks(id),
    objective_id TEXT NOT NULL REFERENCES caf_objectives(id),
    title        TEXT NOT NULL,
    description  TEXT,
    sort_order   INT NOT NULL DEFAULT 0
);

-- Idempotent migrations for databases created before v3 multi-framework support
ALTER TABLE caf_objectives ADD COLUMN IF NOT EXISTS framework_id TEXT NOT NULL DEFAULT 'caf';
ALTER TABLE caf_principles ADD COLUMN IF NOT EXISTS framework_id TEXT NOT NULL DEFAULT 'caf';

-- ---------- Questions & weighted answer options ----------
CREATE TABLE IF NOT EXISTS questions (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    principle_id  TEXT NOT NULL REFERENCES caf_principles(id),
    code          TEXT UNIQUE NOT NULL,      -- e.g. 'A1.a-Q1'
    text          TEXT NOT NULL,
    -- CAF context/narrative explaining the basis of the question and what 'good' looks like
    guidance      TEXT,
    -- weight = relative importance of this question within its principle
    weight        NUMERIC(5,2) NOT NULL DEFAULT 1.0,
    sort_order    INT NOT NULL DEFAULT 0
);

-- Each answer option carries a score 0.0 - 1.0 (fraction of full marks)
CREATE TABLE IF NOT EXISTS answer_options (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    question_id UUID NOT NULL REFERENCES questions(id) ON DELETE CASCADE,
    label       TEXT NOT NULL,               -- 'Achieved','Partially','Not achieved'
    score       NUMERIC(4,3) NOT NULL,       -- 1.000 / 0.500 / 0.000
    sort_order  INT NOT NULL DEFAULT 0
);

-- ---------- Remediation library + mapping ----------
CREATE TABLE IF NOT EXISTS remediation_suggestions (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title       TEXT NOT NULL,
    detail      TEXT NOT NULL,
    -- priority drives roadmap ordering (1 = do first)
    priority    INT NOT NULL DEFAULT 3,
    effort      TEXT NOT NULL DEFAULT 'medium'  -- low | medium | high
);

-- Map a principle (and a "fail" threshold) to a remediation.
-- When a principle's normalised score < threshold, the remediation is queued.
CREATE TABLE IF NOT EXISTS remediation_mappings (
    id             UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    principle_id   TEXT NOT NULL REFERENCES caf_principles(id),
    remediation_id UUID NOT NULL REFERENCES remediation_suggestions(id),
    threshold      NUMERIC(4,3) NOT NULL DEFAULT 0.700,  -- below => triggered
    UNIQUE (principle_id, remediation_id)
);

-- ---------- Assessments ----------
CREATE TABLE IF NOT EXISTS assessments (
    id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id           UUID NOT NULL REFERENCES users(id),
    framework_id      TEXT NOT NULL DEFAULT 'caf' REFERENCES frameworks(id),
    organisation_name TEXT NOT NULL,
    assessment_date   DATE NOT NULL DEFAULT CURRENT_DATE,
    status            TEXT NOT NULL DEFAULT 'in_progress', -- in_progress | completed
    overall_score     NUMERIC(5,2),        -- 0-100, latest computed score
    created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);
ALTER TABLE assessments ADD COLUMN IF NOT EXISTS framework_id TEXT NOT NULL DEFAULT 'caf';

CREATE TABLE IF NOT EXISTS assessment_answers (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    assessment_id UUID NOT NULL REFERENCES assessments(id) ON DELETE CASCADE,
    question_id   UUID NOT NULL REFERENCES questions(id),
    option_id     UUID NOT NULL REFERENCES answer_options(id),
    UNIQUE (assessment_id, question_id)
);

-- Snapshot of per-principle scores when an assessment is completed
CREATE TABLE IF NOT EXISTS assessment_principle_scores (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    assessment_id UUID NOT NULL REFERENCES assessments(id) ON DELETE CASCADE,
    principle_id  TEXT NOT NULL REFERENCES caf_principles(id),
    score         NUMERIC(5,2) NOT NULL,    -- 0-100
    UNIQUE (assessment_id, principle_id)
);

-- Snapshot of per-objective scores when an assessment is completed
CREATE TABLE IF NOT EXISTS assessment_objective_scores (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    assessment_id UUID NOT NULL REFERENCES assessments(id) ON DELETE CASCADE,
    objective_id  TEXT NOT NULL REFERENCES caf_objectives(id),
    score         NUMERIC(5,2) NOT NULL,    -- 0-100
    UNIQUE (assessment_id, objective_id)
);

-- Queued corrective actions (the future ITSM push target)
CREATE TABLE IF NOT EXISTS remediation_actions (
    id             UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    assessment_id  UUID NOT NULL REFERENCES assessments(id) ON DELETE CASCADE,
    principle_id   TEXT NOT NULL REFERENCES caf_principles(id),
    remediation_id UUID NOT NULL REFERENCES remediation_suggestions(id),
    principle_score NUMERIC(5,2) NOT NULL,
    status         TEXT NOT NULL DEFAULT 'queued', -- queued | exported | done
    -- Future ITSM integration fields (Jira / ServiceNow)
    external_ref   TEXT,
    external_system TEXT,
    created_at     TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ---------- Evidence & notes (v3) ----------
-- Free-text notes and/or uploaded evidence files attached to an assessment,
-- either at the assessment level (question_id NULL) or against a specific
-- question. Every row is date/time-stamped and records the acting user;
-- each addition is also written to the audit log.
CREATE TABLE IF NOT EXISTS assessment_evidence (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    assessment_id UUID NOT NULL REFERENCES assessments(id) ON DELETE CASCADE,
    question_id   UUID REFERENCES questions(id),   -- NULL = assessment-level
    kind          TEXT NOT NULL DEFAULT 'note',    -- note | evidence(file)
    content       TEXT,                            -- note text / file description
    file_name     TEXT,                            -- original filename (evidence)
    file_path     TEXT,                            -- stored path in evidence volume
    mime_type     TEXT,
    file_size     BIGINT,
    created_by    TEXT NOT NULL,                   -- acting username
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ---------- Score revisions (v3) ----------
-- Immutable snapshot of an assessment's scores + answers taken every time it
-- is scored. Revision 1 = first completion; each re-score appends a new
-- revision so the ORIGINAL result is always retained for the audit trail and
-- the report can show previous-vs-new maturity.
CREATE TABLE IF NOT EXISTS assessment_revisions (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    assessment_id UUID NOT NULL REFERENCES assessments(id) ON DELETE CASCADE,
    revision_no   INT NOT NULL,
    label         TEXT,
    overall_score NUMERIC(5,2),
    snapshot      JSONB NOT NULL,           -- objective/principle scores, answers, counts
    triggered_by  TEXT NOT NULL,            -- acting username
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (assessment_id, revision_no)
);

-- ---------- Audit log ----------
CREATE TABLE IF NOT EXISTS audit_log (
    id         BIGSERIAL PRIMARY KEY,
    ts         TIMESTAMPTZ NOT NULL DEFAULT now(),
    username   TEXT,                  -- may be null on failed login of unknown user
    action     TEXT NOT NULL,         -- LOGIN_SUCCESS, LOGIN_FAILED, ASSESSMENT_STARTED ...
    detail     TEXT,
    ip_address TEXT
);

-- Helpful indexes
CREATE INDEX IF NOT EXISTS idx_assessments_user ON assessments(user_id);
CREATE INDEX IF NOT EXISTS idx_answers_assessment ON assessment_answers(assessment_id);
CREATE INDEX IF NOT EXISTS idx_actions_assessment ON remediation_actions(assessment_id);
CREATE INDEX IF NOT EXISTS idx_evidence_assessment ON assessment_evidence(assessment_id);
CREATE INDEX IF NOT EXISTS idx_revisions_assessment ON assessment_revisions(assessment_id);
CREATE INDEX IF NOT EXISTS idx_objectives_framework ON caf_objectives(framework_id);
CREATE INDEX IF NOT EXISTS idx_principles_framework ON caf_principles(framework_id);
CREATE INDEX IF NOT EXISTS idx_audit_ts ON audit_log(ts DESC);